package com.fare.taluo.queue;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import jxl.write.WritableWorkbook;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFCellUtil;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.Region;

import com.fare.taluo.io.excel.ExcelExportClassicStyle;
import com.fare.taluo.vo.core.AgentEntityVO;
import com.fare.taluo.vo.taobaoPriceRubik.TaobaoPriceRubikVO;
import com.fare.util.ReportProperties;

public class ExcelTaobao extends ExcelExportClassicStyle {
			;
			//出发城市和到达城市的name
			private String fromAndToCityName;
	
	@SuppressWarnings({ "unchecked", "rawtypes", "deprecation" })
	public static void taobaoExcel(String title, Map<String, Map<String ,List<TaobaoPriceRubikVO>>> datas,String grabStrList) throws Exception {
		/*
		 * title为sheet的名字
		 * 第一个Map中的String：放航段
		 * 第二个Map中的String：放日期时间，第N天
		 * 大List放每个航段的数据，
		 * 后面那个list存放日期后对应的数据
		 */
		// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet(title);
		// /*设置Excel单元格行高、列宽*/
		// sheet.setDefaultRowHeightInPoints(10);
		// sheet.setDefaultColumnWidth((short) 20);
		
		// 设置单元格类型
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
		cellStyle.setWrapText(true);
		// 边框样式
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); // 右边框
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); // 下边框
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle.setLeftBorderColor(HSSFColor.PLUM.index);
		// 设置字体
		HSSFFont font = workbook.createFont();
		font.setFontName("微软雅黑");
		font.setFontHeightInPoints((short) 11);// 设置字体大小
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗字体

		// ******************************第一行头部 第XX天
		HSSFCellStyle cellStyle2 = workbook.createCellStyle();
		cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中
		cellStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
		cellStyle2.setWrapText(true);
		// 边框样式
		cellStyle2.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); // 右边框
		cellStyle2.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); // 下边框
		cellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle2.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND );//前景色(此前景色为纯色：SOLID_FOREGROUND)，在前景色的基本下才能设定背景颜色
		cellStyle2.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
		// 设置字体
		HSSFFont font2 = workbook.createFont();
		font2.setFontName("微软雅黑");
		font2.setFontHeightInPoints((short) 9);// 设置字体大小

		// ******************************第2行
		HSSFCellStyle cellStyle3 = workbook.createCellStyle();
		cellStyle3.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中
		cellStyle3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
		cellStyle3.setWrapText(true);
		// 边框样式
		cellStyle3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); // 右边框
		cellStyle3.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
		cellStyle3.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
		cellStyle3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
		
		// 设置字体
		HSSFFont font3 = workbook.createFont();
		font3.setFontName("微软雅黑");
		font3.setFontHeightInPoints((short) 9);// 设置字体大小
		font3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗字体

		// 设置航线字体
		HSSFCellStyle cellStyle_flighNo = workbook.createCellStyle();
		cellStyle_flighNo.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中
		cellStyle_flighNo.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
		cellStyle_flighNo.setWrapText(true);
		cellStyle_flighNo.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); // 右边框
		cellStyle_flighNo.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
		cellStyle_flighNo.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
		cellStyle_flighNo.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
		cellStyle_flighNo
				.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
		HSSFFont font3_flighNo = workbook.createFont();
		font3_flighNo.setFontName("微软雅黑");
		font3_flighNo.setFontHeightInPoints((short) 9);// 设置字体大小
		font3_flighNo.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗字体
		font3_flighNo.setColor(HSSFColor.DARK_BLUE.index);// 字体颜色
		// 设置taobao字体
		HSSFCellStyle cellStyle_taobao = workbook.createCellStyle();
		cellStyle_taobao.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中
		cellStyle_taobao.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
		cellStyle_taobao.setWrapText(true);
		cellStyle_taobao.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); // 右边框
		cellStyle_taobao.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
		cellStyle_taobao.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
		cellStyle_taobao.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
		cellStyle_taobao.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);//背景颜色
		HSSFFont font3_taobao = workbook.createFont();
		font3_taobao.setFontName("微软雅黑");
		font3_taobao.setFontHeightInPoints((short) 9);// 设置字体大小
		font3_taobao.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗字体
		font3_taobao.setColor(HSSFColor.RED.index);// 字体颜色

		// ******************************第3行之后，动态生成的数据，和表格
		/*
		 * 航段
		 */
		HSSFCellStyle cellStyles_lines = workbook.createCellStyle();
		cellStyles_lines.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中
		cellStyles_lines.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
		cellStyles_lines.setWrapText(true);
		// 边框样式
		cellStyles_lines.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框
		cellStyles_lines.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); // 下边框
		cellStyles_lines.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyles_lines.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
		cellStyles_lines.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND );//前景色(此前景色为纯色：SOLID_FOREGROUND)，在前景色的基本下才能设定背景颜色
		cellStyles_lines.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
		// 设置字体_lines
		HSSFFont fonts_lines = workbook.createFont();
		fonts_lines.setFontName("微软雅黑");
		fonts_lines.setFontHeightInPoints((short) 9);// 设置字体大小
		fonts_lines.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗字体

		//remakr
		HSSFCellStyle cellStyles_remakr = workbook.createCellStyle();
		cellStyles_remakr.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 水平布局：居左
		cellStyles_remakr.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
		cellStyles_remakr.setWrapText(true);
		HSSFFont fonts_remakr = workbook.createFont();
		fonts_remakr.setFontName("微软雅黑");
		fonts_remakr.setFontHeightInPoints((short) 12);// 设置字体大小
		fonts_remakr.setColor(HSSFColor.RED.index);
		
		// 设置字体_others
		HSSFFont fonts_others = workbook.createFont();
		fonts_others.setFontName("微软雅黑");
		fonts_others.setFontHeightInPoints((short) 9);// 设置字体大小

		// ************************************************************
		// 产生表格行
		HSSFRow row = sheet.createRow((short) 0);// 第1行
		HSSFRow row_2 = sheet.createRow((short) 1);// 第2行
		row_2.setHeightInPoints((short) 55);// 设置ex单元格的高度
		HSSFCell cell = row.createCell((short) 0);
		// 单元格合并,四个参数分别是：起始行，起始列，结束行，结束列
		sheet.addMergedRegion(new Region(0, (short) 0, 1, (short) 1));
		cell.setCellStyle(cellStyle);
		cellStyle.setFont(font);
		cell.setCellValue("航线");
		setRegionStyle(sheet, new Region(0, (short) 0, 1, (short) 1), cellStyle);
		
		HSSFCell cell2 = row.createCell((short) 2);
		sheet.addMergedRegion(new Region(0, (short) 2, 0, (short) 5));
		cell2.setCellStyle(cellStyle2);
		cellStyle2.setFont(font2);
		cell2.setCellValue("(+7)("+com.fare.taluo.util.DateUtils.formatFutureDay(new Date(), "MM.dd", 7)+")");
		setRegionStyle(sheet, new Region(0, (short) 2, 0, (short) 5),
				cellStyle2);

		// 未来第16天EXCEL 头样式
		HSSFCell cell3 = row.createCell((short) 6);
		sheet.addMergedRegion(new Region(0, (short) 6, 0, (short) 9));
		cell3.setCellStyle(cellStyle2);
		// cellStyle2.setFont(font2);
		cell3.setCellValue("(+16)("+com.fare.taluo.util.DateUtils.formatFutureDay(new Date(), "MM.dd", 16)+")");
		setRegionStyle(sheet, new Region(0, (short) 6, 0, (short) 9),
				cellStyle2);

		// 未来第34天EXCEL 头样式
		HSSFCell cell4 = row.createCell((short) 10);
		sheet.addMergedRegion(new Region(0, (short) 10, 0, (short) 13));
		cell4.setCellStyle(cellStyle2);
		// cellStyle2.setFont(font2);
		cell4.setCellValue("(+34)("+com.fare.taluo.util.DateUtils.formatFutureDay(new Date(), "MM.dd", 34)+")");
		setRegionStyle(sheet, new Region(0, (short) 10, 0, (short) 13),
				cellStyle2);

		// **********************操作第2行
		int NO = 2;
		for (int m = 0; m < 3; m++) { // 循环3次生成第二行数据

			HSSFCell cell_3 = row_2.createCell((short) NO);
			cell_3.setCellStyle(cellStyle_flighNo);
			cellStyle_flighNo.setFont(font3_flighNo);
			cell_3.setCellValue("航班号");

			HSSFCell cell_4 = row_2.createCell((short) NO + 1);
			cell_4.setCellStyle(cellStyle3);
			cellStyle3.setFont(font3);
			cell_4.setCellValue("代理人最低价(含20元保险）");

			HSSFCell cell_5 = row_2.createCell((short) NO + 2);
			cell_5.setCellStyle(cellStyle3);
			cellStyle3.setFont(font3);
			cell_5.setCellValue("旗舰店");

			HSSFCell cell_6 = row_2.createCell((short) NO + 3);
			cell_6.setCellStyle(cellStyle_taobao);
			cellStyle_taobao.setFont(font3_taobao);
			cell_6.setCellValue("旗舰店-淘宝差价");

			NO = NO + 4;
		}
		
		/*
		 * 解释传过的datas值
		*/		
		int flightMax = 0;// 【航段】数量
		
		int startRows = 2; //开始执行的行
		int restartRows = 2; //开始执行的行
		int startCells = 0; //开始执行的列
		int length_7=0,length_16=0,length_34=0;//存放每一【航段】，【航班号】个数；也就是每个【航段】下，每个【日期】的个数
		String fromAndToCityName=null;//出发城市和到达城市的名字
		
		flightMax=datas.keySet().size();
		// **********************操作第2行
		/**
		 * 1.动态插入数据，同时根据数据量动态生成表格
		 * 2.根据传入的vo来判断，数据量（vo包括：三组数据，分别是+7，+16，+34）
		 * 3.数据传入时统计每条航线最大数据index，用来设置表格（主要用来合并表格），其他数据按条件插入到表格中
		 */

		// 创建14列，其中2头两列是放【出发城市】【到达城市】，后面依次，【航班号】【代理人最底价】【旗舰店】【旗舰店-淘宝差价】
		// 1.查找 Hbase数据，并包装成VO
		// 2.统计出HBASE数据，取得相关需要的数据（某【航线】数据量最大数据numMax，每条航线有多少条【航班号】lineMax【差价】）
		
		HSSFRow rows=null;
		HSSFRow rowsdate=null;
		Map<String ,List<TaobaoPriceRubikVO>> datasValueList=new java.util.HashMap<String ,List<TaobaoPriceRubikVO>>();
//		Map<String ,List<TaobaoPriceRubikVO>> datasListMap=new java.util.HashMap<String ,List<TaobaoPriceRubikVO>>();
		List<TaobaoPriceRubikVO> datasListVo=new ArrayList<TaobaoPriceRubikVO>();
		TaobaoPriceRubikVO taobaoVO=new TaobaoPriceRubikVO();
		/*
		 * 1.根据【航段】来循环写入数据到excel（此时也动态生成excel）
		*/
		HSSFCellStyle cellStyles_others = null;
//		HSSFFont fonts_others =null;
		int decideColor=0;//用于决定颜色参数
		String [] grabDateFT=grabStrList.split("_");
		String fromGrabDates=grabDateFT[0];
		String toGrabDates=grabDateFT[1];
		for(Map.Entry<String,Map<String ,List<TaobaoPriceRubikVO>>> datasMap:datas.entrySet()){
			decideColor++;
			if(decideColor % 2==0){
				cellStyles_others = workbook.createCellStyle();
				cellStyles_others.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中
				cellStyles_others.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
				cellStyles_others.setWrapText(true);
				cellStyles_others.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框
				cellStyles_others.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
				cellStyles_others.setBorderLeft(HSSFCellStyle.BORDER_THIN);
				cellStyles_others.setBorderTop(HSSFCellStyle.BORDER_THIN);
				cellStyles_others.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND );//前景色(此前景色为纯色：SOLID_FOREGROUND)，在前景色的基本下才能设定背景颜色
				cellStyles_others.setFillForegroundColor(new HSSFColor.LIGHT_TURQUOISE().getIndex());
			}else{
				cellStyles_others = workbook.createCellStyle();
				cellStyles_others.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中
				cellStyles_others.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
				cellStyles_others.setWrapText(true);
				cellStyles_others.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框
				cellStyles_others.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
				cellStyles_others.setBorderLeft(HSSFCellStyle.BORDER_THIN);
				cellStyles_others.setBorderTop(HSSFCellStyle.BORDER_THIN);
				cellStyles_others.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND );//前景色(此前景色为纯色：SOLID_FOREGROUND)，在前景色的基本下才能设定背景颜色
				cellStyles_others.setFillForegroundColor(new HSSFColor.LIGHT_CORNFLOWER_BLUE().getIndex());
			}
			// 首先生成【航段】表格，并实现合并表格，而且在表格插入对应的数据
			// 初始化行——建行
			//计算统计出最大【航班】数,也就是根据VOlist中size的最大值班，来决定最大行数
			/*
			 * 2.根据相应的【航段】得到相应的的Map数据
			*/			
			datasValueList=datasMap.getValue();//Map<String ,List<TaobaoPriceRubikVO>>
			int lineMax =0;// 【航班号】数量
			
		//	System.out.println("fromAndToCityName=datasMap.getKey()="+datasMap.getKey());
			for(Map.Entry<String ,List<TaobaoPriceRubikVO>>datasValueMapfor:datasValueList.entrySet()){
				int listSize=datasValueMapfor.getValue().size();
				//存放每段【日期】下的航班号
				if(datasValueMapfor.getKey()=="7"){
					length_7=listSize;
				}if(datasValueMapfor.getKey()=="16"){
					length_16=listSize;
				}if(datasValueMapfor.getKey()=="34"){
					length_34=listSize;
				}
				//获得最大航班号数量				
				if(listSize>lineMax){
					lineMax=listSize;
				}
			}
			
			//System.out.println("startRows="+startRows);
			int con=0;
			int startCellsIn =0;//开始执行的列数
			for(int lines=0;lines<lineMax;lines++){
				con++;
			//	System.out.println("startRows="+startRows);
				rows= sheet.createRow((short) startRows);//startRows=2 第3行开始
				
				HSSFCell cells = rows.createCell((short) startCells);
				HSSFCell cells_2 = rows.createCell((short) (startCells + 1));
				fromAndToCityName=datasMap.getKey();
			 	String[] fromAndToCityNames=fromAndToCityName.split("-");
			 	String fromCityName=fromAndToCityNames[0];
			 	String toCityName=fromAndToCityNames[1];
			 	cells.setCellValue(fromCityName);
			 	cells_2.setCellValue(toCityName);
				
			///	System.out.println("+7="+length_7+" +16="+length_16+" +34="+length_34);
			 	int flag_7=0,flag_16=0,flag_34=0;
				for(Map.Entry<String ,List<TaobaoPriceRubikVO>>datasValueMapallList:datasValueList.entrySet()){
					
					
					//单独处理【日期】对应下没有数据的行
					int VSjudgeSize=99;
					
				//	System.out.println("key=="+datasValueMapallList.getKey());
					if(datasValueMapallList.getKey()=="7"){
						VSjudgeSize=length_7;
						startCellsIn=2;
						flag_7++;
					}if(datasValueMapallList.getKey()=="16"){
						VSjudgeSize=length_16;
						startCellsIn=6;
						flag_16++;
					}if(datasValueMapallList.getKey()=="34"){
						VSjudgeSize=length_34;
						startCellsIn=10;
						flag_34++;
					}
					HSSFCell cell_lines = rows.createCell((short) startCellsIn);
					
					if(con>VSjudgeSize ){//如果行数不大于当前，（行数不够）就补空格
						cell_lines.setCellValue("－");
						cell_lines.setCellStyle(cellStyles_others);
					//	System.out.println("没有数据——行＝"+startRows+"列＝"+startCellsIn);
						
						HSSFCell cell_downPrice = rows.createCell((short) startCellsIn + 1);
						cell_downPrice.setCellValue("－");
						cell_downPrice.setCellStyle(cellStyles_others);
					//	System.out.println("没有数据_行＝"+startRows+"列＝"+(startCellsIn+1));
						
						HSSFCell cell_shipFlag = rows.createCell((short) startCellsIn + 2);
						cell_shipFlag.setCellValue("－");
						cell_shipFlag.setCellStyle(cellStyles_others);
				//		System.out.println("行＝"+startRows+"列＝"+(startCellsIn+2));
						// 【旗舰店-淘宝差价】
						HSSFCell cell_gapPrice = rows.createCell((short) startCellsIn + 3);
						cell_gapPrice.setCellValue("－");
						cell_gapPrice.setCellStyle(cellStyles_others);
				//		System.out.println("行＝"+startRows+"列＝"+(startCellsIn+3));
						cellStyles_others.setFont(fonts_others);
						
					}else{
					taobaoVO=	datasValueMapallList.getValue().get(lines);
					if(taobaoVO.getFlightId().length()>0 || !taobaoVO.getFlightId().equals("")){
					cell_lines.setCellValue(taobaoVO.getFlightId());
					}else{
						cell_lines.setCellValue("－");
					}
					cell_lines.setCellStyle(cellStyles_others);
				//	System.out.println("行＝"+startRows+"列＝"+startCellsIn);
					// 【代理人最低价】
					HSSFCell cell_downPrice = rows.createCell((short) startCellsIn + 1);
					if((taobaoVO.getLowerPrice().length()>0 && !taobaoVO.getLowerPrice().equals("") && taobaoVO.getLowerPrice() !=null )&& !taobaoVO.getLowerPrice().equals("null")){
						cell_downPrice.setCellValue((Integer.parseInt(taobaoVO.getLowerPrice().split("\\.")[0]))+"");
					}else{
						cell_downPrice.setCellValue("－");
					}
					cell_downPrice.setCellStyle(cellStyles_others);
			//		System.out.println("行＝"+startRows+"列＝"+(startCellsIn+1));
					// 【旗舰店】
					HSSFCell cell_shipFlag = rows.createCell((short) startCellsIn + 2);
					if((taobaoVO.getAgentLowerPrice().length()>0 && !taobaoVO.getAgentLowerPrice().equals("") && taobaoVO.getAgentLowerPrice()!=null) && !taobaoVO.getAgentLowerPrice().equals("null")){
						cell_shipFlag.setCellValue(Integer.parseInt(taobaoVO.getAgentLowerPrice().split("\\.")[0])+"");
					}else{
						cell_shipFlag.setCellValue("－");
					}
					cell_shipFlag.setCellStyle(cellStyles_others);
				//	System.out.println("行＝"+startRows+"列＝"+(startCellsIn+2));
					// 【旗舰店-淘宝差价】
					HSSFCell cell_gapPrice = rows.createCell((short) startCellsIn + 3);
					if((taobaoVO.getAgentLowerPrice().length()>0 && !taobaoVO.getAgentLowerPrice().equals("") && taobaoVO.getAgentLowerPrice() !=null) && !taobaoVO.getAgentLowerPrice().equals("null")){
						cell_gapPrice.setCellValue((taobaoVO.getDifferencePirce()).toString());
					}else{
						cell_gapPrice.setCellValue("－");
					}
					cell_gapPrice.setCellStyle(cellStyles_others);
				//	System.out.println("行＝"+startRows+"列＝"+(startCellsIn+3));
					cellStyles_others.setFont(fonts_others);
					}}
				
					if(flag_34 ==0) {
						flag_34++;
						startCellsIn=10;
						
						seTNullCells(rows,cellStyles_others,startCellsIn);
						cellStyles_others.setFont(fonts_others);
					}if(flag_16 ==0){
						flag_16++;
						startCellsIn=6;
						
						seTNullCells(rows,cellStyles_others,startCellsIn);
						cellStyles_others.setFont(fonts_others);
					}if(flag_7 ==0){
						flag_7++;
						startCellsIn=2;
						
						seTNullCells(rows,cellStyles_others,startCellsIn);
						cellStyles_others.setFont(fonts_others);
					}
					startRows++;
			}
			
				// 单元格合并,四个参数分别是：起始行，起始列，结束行，结束列
			// 	System.out.println("endrows="+(startRows));
			// 	System.out.println("start=="+restartRows);
				Region regions = new Region(restartRows, (short) 0,(startRows -1), (short) 0);
				Region regions_2 = new Region(restartRows, (short) 1,(startRows -1), (short) 1);
				sheet.addMergedRegion(regions);
				setRegionStyle(sheet, regions, cellStyles_lines);
				sheet.addMergedRegion(regions_2);
				setRegionStyle(sheet, regions_2, cellStyles_lines);
				cellStyles_lines.setFont(fonts_lines);
			  	restartRows=startRows;
		}
		//存放【备注】
		restartRows=startRows+3;
		HSSFRow remarkRows = sheet.createRow((short) restartRows);
		HSSFCell remarkCells = remarkRows.createCell((short) 0);
		Region regions_remakr=new Region(restartRows, (short) 0,restartRows, (short) 13);
		sheet.addMergedRegion(regions_remakr);
		setRegionStyle(sheet,regions_remakr, cellStyles_remakr);
		cellStyles_remakr.setFont(fonts_remakr);
		//remarkCells.setCellValue("备注：此报表数据抓取时间从 "+fromGrabDates+" 到 "+toGrabDates);
		remarkCells.setCellValue("");

		// 存盘test
//		String strFilePath="C:/";
		String strFilePath = ReportProperties.getInstance().getSavePath().endsWith("/")? ReportProperties.getInstance().getSavePath() :	ReportProperties.getInstance().getSavePath()+"/";
		
		try {
			if (!(new File(strFilePath).isDirectory())) {
				new File(strFilePath).mkdirs();
			}
			strFilePath = strFilePath + title+".xls";
//			strFilePath = "c:\\test.xls";
			File fTarget = new File(strFilePath);
			FileOutputStream fileOut = new FileOutputStream(fTarget);
			try {
				workbook.write(fileOut);
				fileOut.close();
				System.out.println("SUCCESS");
			} catch (IOException e) {
				e.printStackTrace();
				e.getMessage();
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}

	}
	
	
	public static void seTNullCells(HSSFRow rows,HSSFCellStyle cellStyles_others ,int startCellsIn){
		
		HSSFCell cell_lines = rows.createCell((short) startCellsIn);
		cell_lines.setCellValue("－");
		cell_lines.setCellStyle(cellStyles_others);
		
		HSSFCell cell_downPrice = rows.createCell((short) startCellsIn + 1);
		cell_downPrice.setCellValue("－");
		cell_downPrice.setCellStyle(cellStyles_others);
		
		HSSFCell cell_shipFlag = rows.createCell((short) startCellsIn + 2);
		cell_shipFlag.setCellValue("－");
		cell_shipFlag.setCellStyle(cellStyles_others);
		
		HSSFCell cell_gapPrice = rows.createCell((short) startCellsIn + 3);
		cell_gapPrice.setCellValue("－");
		cell_gapPrice.setCellStyle(cellStyles_others);
		
	}

	
	/**
	 * 合并单元格加边框 水平
	 * 
	 * @param sheet
	 * @param region
	 * @param cs
	 */
	public static void setCellBorder(int start, int end, HSSFRow row,
			HSSFCellStyle style) {
		for (int i = start; i <= end; i++) {
			HSSFCell cell = row.createCell(i);
			cell.setCellValue("");
			cell.setCellStyle(style);
		}
	}

	/**
	 * 合并单元格加边框 垂直
	 * 
	 * @param sheet
	 * @param region
	 * @param cs
	 */
	
	/*public static void setCellBorder(int start, int end, int col, HSSFSheet
	  sheet, HSSFCellStyle style) { 
		for(int i=start;i<=end;i++){ 
			HSSFRow row =org.apache.poi.hssf.usermodel.HSSFRow.ExcelUtil.createRow(sheet, start, (short)0); 
			HSSFCell cell =row.createCell(col); 
			cell.setCellValue(""); 
			cell.setCellStyle(style); 
			} 
		}*/
	
	/**
	 * 合并单元格加边框,通用垂直水平
	 * 
	 * @param sheet
	 * @param region
	 * @param cs
	 */
	public static void setRegionStyle(HSSFSheet sheet, Region region,
			HSSFCellStyle cs) {
		int toprowNum = region.getRowFrom();
		for (int i = region.getRowFrom(); i <= region.getRowTo(); i++) {
			HSSFRow row = HSSFCellUtil.getRow(i, sheet);
			for (int j = region.getColumnFrom(); j <= region.getColumnTo(); j++) {
				HSSFCell cell = HSSFCellUtil.getCell(row, (short) j);
				cell.setCellStyle(cs);
			}
		}
	}

	@Override
	protected void fillSheet(WritableWorkbook workBook) throws Exception {
		// TODO Auto-generated method stub

	}

	public static void main(String[] args) {
//		Map<String,List<Map<String ,TaobaoPriceRubikVO>>> listValue =new java.util.HashMap<String,List<Map<String ,TaobaoPriceRubikVO>>>();
		Map <String,Map<String ,List<TaobaoPriceRubikVO>>> listValue=new java.util.HashMap<String,Map<String ,List<TaobaoPriceRubikVO>>>();
		Map<String ,List<TaobaoPriceRubikVO>> datasValueMap=new java.util.HashMap<String ,List<TaobaoPriceRubikVO>>();
		List<TaobaoPriceRubikVO> datasListVo=new ArrayList<TaobaoPriceRubikVO>();
		List<TaobaoPriceRubikVO> datasListVo2=new ArrayList<TaobaoPriceRubikVO>();
		List<TaobaoPriceRubikVO> datasListVo3=new ArrayList<TaobaoPriceRubikVO>();
		TaobaoPriceRubikVO taobaoVoTest=new TaobaoPriceRubikVO();
		TaobaoPriceRubikVO taobaoVoTest2=new TaobaoPriceRubikVO();
		TaobaoPriceRubikVO taobaoVoTest3=new TaobaoPriceRubikVO();
		AgentEntityVO agentVo=new AgentEntityVO();
		AgentEntityVO agentVo2=new AgentEntityVO();
		AgentEntityVO agentVo3=new AgentEntityVO();
		taobaoVoTest.setFromAndTocityKey("PEKCTU");
//		taobaoVoTest.setFromAndCityName("北京-成都");
		agentVo.setPrice("800");
		taobaoVoTest.setAgentLowerPrice("400.0");
		taobaoVoTest.setLowerPrice("900.0");
		taobaoVoTest.setCarrierKey("CZ");
		taobaoVoTest.setCarrierName("南方航空公司");
		taobaoVoTest.setFlightId("CZ1111");
		
		taobaoVoTest2.setFromAndTocityKey("CANCTU");
//		taobaoVoTest2.setFromAndCityName("广州-成都");
		agentVo2.setPrice("500.0");
		taobaoVoTest2.setAgentLowerPrice("200");
		taobaoVoTest2.setLowerPrice("600.0");
		taobaoVoTest2.setCarrierKey("MU");
		taobaoVoTest2.setCarrierName("东方航空公司");
		taobaoVoTest2.setFlightId("MU222");
		
		taobaoVoTest3.setFromAndTocityKey("PEKCTU");
//		taobaoVoTest3.setFromAndCityName("北京-深圳");
		agentVo3.setPrice("1800.0");
		taobaoVoTest3.setAgentLowerPrice("100.0");
		taobaoVoTest3.setLowerPrice("2900");
		taobaoVoTest3.setCarrierKey("CA");
		taobaoVoTest3.setCarrierName("中国国际航空公司");
		taobaoVoTest3.setFlightId("CA333");
		
		datasListVo.add( taobaoVoTest);
		datasListVo.add( taobaoVoTest2);
		datasListVo.add( taobaoVoTest3);
		
		datasListVo2.add( taobaoVoTest);
		datasListVo2.add( taobaoVoTest2);
		datasListVo2.add( taobaoVoTest3);
		datasListVo2.add( taobaoVoTest3);
		
		datasListVo3.add( taobaoVoTest);
		datasListVo3.add( taobaoVoTest2);
		datasListVo3.add( taobaoVoTest3);
		datasListVo3.add( taobaoVoTest2);
		
		datasValueMap.put("7", datasListVo);
//		datasValueMap.put("16", datasListVo2);
	//	datasValueMap.put("34", datasListVo3);
					
//		datasValueMap.values().add(datasListVo);
//		listValue.values().add(datasValueMap);
		listValue.put("北京-成都", datasValueMap);
//		listValue.put("广州-成都", datasValueMap);
//		listValue.put("北京-深圳", datasValueMap);
//		datas.add(e);
		try {
			
			ExcelTaobao.taobaoExcel("test", listValue,"test1_test2");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		/*String a="100";
		String b="100";
		String c=String.valueOf((Integer.parseInt(a)-Integer.parseInt(b)));
		System.out.println("success"+c);*/
	/*	Map<String, String> map = new java.util.HashMap<String, String>();
		map.put("", "1");
		map.put("2", "3");
		map.put("3", "4");
		int sizeKey=map.keySet().size();
		int sizeValue=map.values().size();
		System.out.println("key="+sizeKey+"value="+sizeValue);
		Object[] test1= map.keySet().toArray();
		Arrays.sort(test1);
		 int maxKey = Integer.parseInt((String)test1[test1.length - 1]);
		 for(int i=0;i<maxKey;i++){
			 
		 }
		 String hello=map.get("2");
//		String test=test1.get(0);
		System.out.println(""+hello);
		*/
	}

	
}
